package mypackage.dao.impl;

import mypackage.bean.Address;
import mypackage.bean.Clients;
import mypackage.dao.AddressDao;
import mypackage.utils.JDBCUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

/**
 * @author YH925
 * @create 2021/12/9 22:08
 */
public class AddressDaoImpl implements AddressDao {
    Connection connection = null;

    {
        try {
            connection = JDBCUtils.getConnection();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    QueryRunner runner = new QueryRunner();

    /**
     * 根据用户id查询出用户的收货地址信息
     * @param id 传入当前登录用户的id
     * @return 返回用户的所有收货地址
     */
    @Override
    public List<Address> selectAddressList(int clientId) {
        String sql = "select * from address where clientId = ?";
        BeanListHandler<Address>handler = new BeanListHandler<>(Address.class);
        List<Address> addressList = null;
        try {
            addressList = runner.query(connection, sql, handler, clientId);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return addressList;
    }

    /**
     * 查询收货地址信息，包含用户信息
     * @param clientId 传入用户id
     * @return 连同用户信息一同返回
     */
    @Override
    public List<Address> selectAddressListWithClient(int clientId) {

        String sql = "select * from address where clientId = ?";
        String sql2 = "select * from clients where id = ?";

        BeanListHandler<Address>handler1 = new BeanListHandler<>(Address.class);
        BeanHandler<Clients>handler2 = new BeanHandler<>(Clients.class);

        Clients client = null;
        List<Address> addressList = null;
        try {
            client = runner.query(connection, sql2, handler2, clientId);
            addressList = runner.query(connection, sql, handler1, clientId);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        for (Address address : addressList) {
            address.setClient(client);
        }
        return addressList;
    }

    @Override
    public Address selectAddressById(int addressId) {
        String sql = "select * from address where id = ?";
        BeanHandler<Address>handler = new BeanHandler<>(Address.class);
        Address address = null;
        try {
            address = runner.query(connection, sql, handler, addressId);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return address;
    }

    //新加收货地址
    @Override
    public void insertAddress(int clientId, String acceptAddress, String tel, String acceptName) {
        try {
            connection.setAutoCommit(false);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        String sql = "insert into address values(?,?,?,?,?)";

        try {
            runner.update(connection,sql,null,clientId,acceptAddress,tel,acceptName);
            connection.commit();
        } catch (SQLException e) {
            try {
                connection.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        }
    }

    /**
     * 查询收货地址数量
     * @param clientId
     * @return
     */
    @Override
    public int findAddressAmount(int clientId) {
        String sql = "select count(*) from address where CLIENTID = ?";
        ScalarHandler<Object> handler = new ScalarHandler<>();
        Object obj = null;
        try {
            obj = runner.query(connection, sql, handler, clientId);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        Integer count = Integer.parseInt(obj.toString());
        return count;
    }

    @Override
    public void updateInfo(int id, String newAddress, String newTel, String newName) {
        try {
            connection.setAutoCommit(false);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        String sql = "update address set acceptAddress = ?,tel = ?,accpetName = ? where id = ?";
        try {
            runner.update(connection,sql,newAddress,newTel,newName,id);
            connection.commit();
        } catch (SQLException e) {
            e.printStackTrace();
            try {
                connection.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
    }

    @Override
    public void delAddressById(int addressId) {
        try {
            connection.setAutoCommit(false);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        String sql = "delete from address where id = ?";
        try {
            runner.update(connection,sql,addressId);
            connection.commit();
        } catch (SQLException e) {
            e.printStackTrace();
            try {
                connection.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
    }
}
